-- exec sp_get_All_Approved_Software_Line_Items 3144, 1

ALTER  PROC sp_get_All_Approved_Software_Line_Items ( @salesOrder_Id INT, @lineItemNumber INT )
AS
BEGIN
 	DECLARE @results Table( lineItemId  INT, qtyToShip INT, SalesOrder_ID INT, COOL_Line_Item_No INT, Product_ID INT, COOL_Qty int, COOL_Unit_Cost FLOAT,
 	COOL_Amount FLOAT, COOL_Requested_Date DATETIME, COOL_Booking_Date DATETIME, COOL_Sugg_Schedule_Date_With_Substitutables DATETIME,
 	COOL_Sugg_Schedule_Date_Without_Substitutables DATETIME, COOL_Schedule_Date DATETIME, COOL_Revised_Date DATETIME, COOL_Is_Cancel BIT,
 	COOL_Status VARCHAR(50), COOL_Taxable BIT, COOL_Description VARCHAR(200), COOL_PO_Line_Item_No INT, 
 	Part_Number VARCHAR(50), Mkt_Part_Number VARCHAR(50), Customer_Part_Number VARCHAR(50), Qty_Available INT)
 		
 	DECLARE @temp_lineItem		INT
 	DECLARE @shipped_qty_sum 	INT
 	DECLARE @qty_to_be_shipped 	INT
 
 	DECLARE @COOL_Line_Item_No 	INT
 	DECLARE @Product_ID		INT	
 	DECLARE @COOL_Qty		INT
 	DECLARE @COOL_Unit_Cost		FLOAT
 	DECLARE @COOL_Amount		FLOAT
 	DECLARE @COOL_Requested_Date	DATETIME
 	DECLARE @COOL_Booking_Date	DATETIME
 	DECLARE @COOL_Sugg_Schedule_Date_With_Substitutables    DATETIME
 	DECLARE @COOL_Sugg_Schedule_Date_Without_Substitutables DATETIME
 	DECLARE @COOL_Schedule_Date 	DATETIME
 	DECLARE @COOL_Revised_Date 	DATETIME
 	DECLARE @COOL_Is_Cancel		BIT
 	DECLARE @COOL_Status 		VARCHAR(50)
 	DECLARE @COOL_Taxable 		BIT
 	DECLARE @COOL_Description 	VARCHAR(200)
 	DECLARE @COOL_PO_Line_Item_No 	INT
 
 	DECLARE @mfg_part_number 	VARCHAR(50)
 	DECLARE @mkt_part_number 	VARCHAR(50)
 	DECLARE @cutomer_part_number 	VARCHAR(50)
 	DECLARE @temp_product_type	VARCHAR(50)
 	DECLARE @endUser		VARCHAR(50)
 	DECLARE @customer_id		INT
 	DECLARE @temp_process_step_id   INT
 	DECLARE @parent_product_id	INT
 	DECLARE @qty_available		INT
 
 	SELECT @temp_lineItem = LineItem_ID FROM Customer_Open_Order_LineItem WHERE SalesOrder_ID = @salesOrder_Id and COOL_LINE_ITEM_NO = @lineItemNumber
  		
 	SET @shipped_qty_sum = ( SELECT ISNULL( SUM( CSLIT_Qty_Shipped ), 0 ) FROM Customer_Shipping_Line_Item_Table WHERE LineItem_ID = @temp_lineItem AND CSLIT_Status != 'CANCELLED' )
	SET @qty_to_be_shipped  = ( SELECT ( AVG( COOL_Qty ) - @shipped_qty_sum ) FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @temp_lineItem )
 
 	IF( @qty_to_be_shipped > 0 )
 	BEGIN				
 		SELECT @COOL_Line_Item_No = COOL_Line_Item_No, @Product_ID = Product_ID, 
 		@COOL_Qty = COOL_Qty, @COOL_Unit_Cost = COOL_Unit_Cost, @COOL_Amount = COOL_Amount, 
 		@COOL_Requested_Date = convert (varchar, COOL_Requested_Date, 101), @COOL_Booking_Date = convert (varchar, COOL_Booking_Date, 101), 
 		@COOL_Sugg_Schedule_Date_With_Substitutables = convert (varchar, COOL_Sugg_Schedule_Date_With_Substitutables, 101) , 
 		@COOL_Sugg_Schedule_Date_Without_Substitutables = convert (varchar, COOL_Sugg_Schedule_Date_Without_Substitutables, 101), 
 		@COOL_Schedule_Date = convert (varchar, COOL_Schedule_Date, 101), @COOL_Revised_Date = convert (varchar, COOL_Revised_Date, 101), 
 		@COOL_Is_Cancel = COOL_Is_Cancel, @COOL_Status = COOL_Status, 
 		@COOL_Taxable = COOL_Taxable, @COOL_Description = COOL_Description, 
 		@COOL_PO_Line_Item_No = COOL_PO_Line_Item_No FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @temp_lineItem
 
 		IF( ( @COOL_Schedule_Date IS NULL ) AND ( @COOL_Sugg_Schedule_Date_Without_Substitutables IS NOT NULL ) )
 		BEGIN
 			SET @COOL_Schedule_Date = @COOL_Sugg_Schedule_Date_Without_Substitutables
 		END
 
 		IF( ( @COOL_Sugg_Schedule_Date_Without_Substitutables IS NULL ) AND ( @COOL_Schedule_Date IS NOT NULL ) ) 
 		BEGIN
 			SET @COOL_Sugg_Schedule_Date_Without_Substitutables = @COOL_Schedule_Date
 		END

		SELECT @mfg_part_number = Part_Number, @mkt_part_number = P_Marketing_Part_Number FROM product WHERE Product_Id = @Product_ID
 			
 		-- Fetch CustomerPartNumber if endUser is null fetch the Customer Part number else fetch End user part number
 		SELECT @endUser = SO_EndUser, @customer_id = Customer_id FROM Sales_Order WHERE SalesOrder_ID = @salesOrder_Id
 			
 		IF @endUser = NULL OR LEN( @endUser ) <= 0
 		BEGIN
 			SELECT @cutomer_part_number = ISNULL( Customer_PartNumber, '') FROM Customer_PartNumbers WHERE Product_Id = @Product_ID and Customer_id = @customer_id
 		END
 		ELSE
		BEGIN
 			SELECT @cutomer_part_number = ISNULL( Customer_PartNumber, '') FROM End_User_Part_Numbers WHERE Product_Id = @Product_ID and  End_User_Id = ( Select TOP 1 End_User_Id FROM End_User_Table WHERE E_Name = @endUser and Customer_Id = @customer_id )
		END

		-- THIS IS HARD CODED
		SET @qty_available = 10
		
		INSERT INTO @results( lineItemId, qtyToShip, SalesOrder_ID, COOL_Line_Item_No, Product_ID, COOL_Qty, COOL_Unit_Cost,
 		COOL_Amount, COOL_Requested_Date, COOL_Booking_Date, COOL_Sugg_Schedule_Date_With_Substitutables,
 		COOL_Sugg_Schedule_Date_Without_Substitutables, COOL_Schedule_Date, COOL_Revised_Date, COOL_Is_Cancel,
 		COOL_Status, COOL_Taxable, COOL_Description, COOL_PO_Line_Item_No,Part_Number, Mkt_Part_Number, Customer_Part_Number, Qty_Available ) Values
 		( @temp_lineItem, @qty_to_be_shipped, @SalesOrder_ID, @COOL_Line_Item_No, @Product_ID, @COOL_Qty, @COOL_Unit_Cost,
 		@COOL_Amount, @COOL_Requested_Date, @COOL_Booking_Date, @COOL_Sugg_Schedule_Date_With_Substitutables,
 		@COOL_Sugg_Schedule_Date_Without_Substitutables, @COOL_Schedule_Date, @COOL_Revised_Date, @COOL_Is_Cancel,
 		@COOL_Status, @COOL_Taxable, @COOL_Description, @COOL_PO_Line_Item_No, @mfg_part_number, @mkt_part_number, @cutomer_part_number, @qty_available )
 	END
 	SELECT * FROM @results
END